package com.ccjq.jq173.shosestore.product.dao.impl;

import com.ccjq.jq173.shosestore.core.db.manager.DbConnection;
import com.ccjq.jq173.shosestore.product.dao.productDao;
import com.ccjq.jq173.shosestore.product.po.Category;
import com.ccjq.jq173.shosestore.product.po.Product;
import com.ccjq.jq173.shosestore.product.po.Slider;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class productDaoImpl implements productDao {

    @Override//查询所有品牌
    public List<Category> getPinPai() {
        List<Category>list=new ArrayList<Category>();
        Connection con=null;
        PreparedStatement ps=null;
        try
        {
            con = DbConnection.getConnection();
            ps=con.prepareStatement("select name from t_category");
            ResultSet rs=ps.executeQuery();
            while(rs.next())
            {
                Category c=new Category();
                c.setName(rs.getString(1));
                list.add(c);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public String getPinPai(String categoryId) {
        String s="";
        Connection con=null;
        PreparedStatement ps=null;
        try
        {
            con = DbConnection.getConnection();
            ps=con.prepareStatement("select name from t_category where  category_id=?");
            ps.setString(1,categoryId);
            ResultSet rs=ps.executeQuery();
            while(rs.next())
            {
                s=rs.getString(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return s;
    }

    @Override//查询所有品牌ID
    public String getId(String name) {
        String s="";
        Connection con=null;
        PreparedStatement ps=null;
        try
        {
            con = DbConnection.getConnection();
            ps=con.prepareStatement("select category_id from t_category where  name=?");
            ps.setString(1,name);
            ResultSet rs=ps.executeQuery();
            while(rs.next())
            {
                s=rs.getString(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return s;
    }

    @Override//查询所有鞋的信息
    public List<Product> getAllShoes() {
        List<Product>list=new ArrayList<Product>();
        Connection con=null;
        PreparedStatement ps=null;
        try
        {
            con = DbConnection.getConnection();
            ps=con.prepareStatement("select imgpath,feature,price,product_id from t_product");
            ResultSet rs=ps.executeQuery();
            while(rs.next())
            {
                Product p=new Product();
                p.setImgpath(rs.getString(1));
                p.setFeature(rs.getString(2));
                p.setPrice(rs.getDouble(3));
                p.setProductId(rs.getString(4));
                list.add(p);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List<Product> getAllShoes(int startRow, int pageSize) {
        List<Product> list=null;
        Connection con=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try
        {
            con=DbConnection.getConnection();
            ps=con.prepareStatement("select imgpath,feature,price,product_id from (select * from t_product order by " +
                    "publish_date desc) where product_id not in (select product_id from " +
                    "(select * from t_product order by publish_date desc) where rownum<=?) " +
                    "and rownum <=? ");
            ps.setInt(1,startRow-1);
            ps.setInt(2,pageSize);
            rs=ps.executeQuery();
            list=new ArrayList<Product>();
            while (rs.next())
            {
                Product p=new Product();
                p.setImgpath(rs.getString(1));
                p.setFeature(rs.getString(2));
                p.setPrice(rs.getDouble(3));
                p.setProductId(rs.getString(4));
                System.out.println("p.getProductId() = " + p.getProductId());
                list.add(p);
            }

        }
        catch(SQLException e)
        {
            e.printStackTrace();

        }
        return list;
    }

    @Override
    public List<Product> getAllShoes(int startRow, int pageSize, String categoryId) {
        List<Product> list=null;
        Connection con=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try
        {
            con=DbConnection.getConnection();
            ps=con.prepareStatement("select imgpath,feature,price,product_id from (select * from t_product " +
                    "where category_id=? order by publish_date desc) where product_id not in " +
                    "(select product_id from (select * from t_product  where category_id=?  " +
                    " order by publish_date desc) where rownum<=?) and rownum <=? ");
            ps.setInt(3,startRow-1);
            ps.setInt(4,pageSize);
            ps.setString(1,categoryId);
            ps.setString(2,categoryId);
            rs=ps.executeQuery();
            list=new ArrayList<Product>();
            while (rs.next())
            {
                Product p=new Product();
                p.setImgpath(rs.getString(1));
                p.setFeature(rs.getString(2));
                p.setPrice(rs.getDouble(3));
                p.setProductId(rs.getString(4));
                System.out.println("p.getProductId() = " + p.getProductId());
                list.add(p);
            }

        }
        catch(SQLException e)
        {
            e.printStackTrace();

        }
        return list;
    }

    @Override//查询指定品牌的详细信息
    public Product getOneShoes(String productId) {
        Connection con=null;
        PreparedStatement ps=null;
        Product p=new Product();
        try
        {
            con = DbConnection.getConnection();
            ps=con.prepareStatement("select imgpath,feature,price,model,category_id from t_product where product_id=?");
            ps.setString(1,productId);
            ResultSet rs=ps.executeQuery();
            while(rs.next())
            {
                p.setImgpath(rs.getString(1));
                p.setFeature(rs.getString(2));
                p.setPrice(rs.getDouble(3));
                p.setModel(rs.getString(4));
                p.setCategoryId(rs.getString(5));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return p;
    }

    @Override//查询指定品牌的信息
    public List<Product> getAllShoes(String productId) {
        List<Product>list=new ArrayList<Product>();
        Connection con=null;
        PreparedStatement ps=null;
        try
        {
            con = DbConnection.getConnection();
            ps=con.prepareStatement("select imgpath,feature,price,product_id from t_product where product_id=?");
            ps.setString(1,productId);
            ResultSet rs=ps.executeQuery();
            while(rs.next())
            {
                Product p=new Product();
                p.setImgpath(rs.getString(1));
                p.setFeature(rs.getString(2));
                p.setPrice(rs.getDouble(3));
                p.setProductId(rs.getString(4));
                list.add(p);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List<Product> getShoes(String categoryId) {
        List<Product>list=new ArrayList<Product>();
        Connection con=null;
        PreparedStatement ps=null;
        try
        {
            con = DbConnection.getConnection();
            ps=con.prepareStatement("select product_id,imgpath,feature,price from t_product where category_id=?");
            ps.setString(1,categoryId);
            ResultSet rs=ps.executeQuery();
            while(rs.next())
            {
                Product p=new Product();
                p.setProductId(rs.getString(1));
                p.setImgpath(rs.getString(2));
                p.setFeature(rs.getString(3));
                p.setPrice(rs.getDouble(4));
                list.add(p);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override//查询指定鞋的库存量
    public String getStock(String productId) {
        String stock ="0";
        Connection con=null;
        PreparedStatement ps=null;
        try
        {
            con = DbConnection.getConnection();
            ps=con.prepareStatement("select quantity from t_stock where product_id=?");
            ps.setString(1,productId);
            ResultSet rs=ps.executeQuery();
            while(rs.next())
            {
                stock=rs.getInt(1)+"";
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println("stock="+stock);
        return stock;
    }

    @Override//查询新品
    public List<Product> getNewShoes() {
        List<Product>list=new ArrayList<Product>();
        Connection con=null;
        PreparedStatement ps=null;
        try
        {
            con = DbConnection.getConnection();
            ps=con.prepareStatement("select imgpath,feature,price,product_id from " +
                    "(select * from t_product order by publish_date desc) where rownum<=6");
            ResultSet rs=ps.executeQuery();
            while(rs.next())
            {
                Product p=new Product();
                p.setImgpath(rs.getString(1));
                p.setFeature(rs.getString(2));
                p.setPrice(rs.getDouble(3));
                p.setProductId(rs.getString(4));
                list.add(p);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override//查询热卖的三双鞋
    public List<Product> getHotShoes() {
        List<Product>list=new ArrayList<Product>();
        Connection con=null;
        PreparedStatement ps=null;
        try
        {
            con = DbConnection.getConnection();
            ps=con.prepareStatement("select imgpath,feature,price,a.product_id,b.xl from " +
                    "(select * from t_product) a join (select product_id,sum(quantity) " +
                    "as xl from t_order_product group by product_id order by xl desc) b on " +
                    "a.product_id=b.product_id where rownum<=4");
            ResultSet rs=ps.executeQuery();
            while(rs.next())
            {
                Product p=new Product();
                p.setImgpath(rs.getString(1));
                p.setFeature(rs.getString(2));
                p.setPrice(rs.getDouble(3));
                p.setProductId(rs.getString(4));
                list.add(p);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override//查询滚动条
    public List<Slider> getGunDongShoes() {
        List<Slider>list=new ArrayList<Slider>();
        Connection con=null;
        PreparedStatement ps=null;
        try
        {
            con = DbConnection.getConnection();
            ps=con.prepareStatement("select imgpath,availability,product_id from t_slider where availability=1");
            ResultSet rs=ps.executeQuery();
            while(rs.next())
            {
                Slider s=new Slider();
                s.setImgpath(rs.getString(1));
                s.setAvailability(rs.getInt(2));
                s.setProductId(rs.getString(3));

                list.add(s);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override//根据商品ID查询商品特性描述
    public String get(String productId) {
        String s ="";
        Connection con=null;
        PreparedStatement ps=null;
        try
        {
            con = DbConnection.getConnection();
            ps=con.prepareStatement("select feature from t_product where product_id=?");
            ps.setString(1,productId);
            ResultSet rs=ps.executeQuery();
            while(rs.next())
            {
                s=rs.getString(1)+"";
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return s;
    }

    @Override//更改指定库存量
    public boolean updateStock(int quantity, String productId, Connection con) {
        PreparedStatement pre=null;
        boolean b=false;
        try {

            con=DbConnection.getConnection();
            String sql="update t_stock set quantity=quantity-? where product_id=?";
            pre=con.prepareStatement(sql);
            pre.setString(1, String.valueOf(quantity));
            pre.setString(2,productId);
            int i=pre.executeUpdate();
            b=i>0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return b;
    }

    @Override
    public int getCount() {
        int count=0;
        List<Product> list=new ArrayList<Product>();
        Connection con=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            con = DbConnection.getConnection();
            ps = con.prepareStatement("select count(product_id) from t_product");
            rs = ps.executeQuery();
            rs.next();
            count=rs.getInt(1);
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        return count;
    }

    @Override
    public int getCount(String categoryId) {
        int count=0;
        List<Product> list=new ArrayList<Product>();
        Connection con=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            con = DbConnection.getConnection();
            ps = con.prepareStatement("select count(product_id) from t_product where category_id =?");
            ps.setString(1,categoryId);
            rs = ps.executeQuery();
            rs.next();
            count=rs.getInt(1);
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        return count;
    }

    public Product getProduct(String productId)
    {
        Product s =null;
        Connection con=null;
        PreparedStatement ps=null;
        try
        {
            con = DbConnection.getConnection();
            ps=con.prepareStatement("select * from t_product where product_id=?");
            ps.setString(1,productId);
            ResultSet rs=ps.executeQuery();
            while(rs.next())
            {
                s=new Product();
                s.setProductId(rs.getString(1));
                s.setDescription(rs.getString(2));
                s.setPrice(rs.getDouble(3));
                s.setModel(rs.getString(4));
                s.setFeature(rs.getString(5));
                s.setImgpath(rs.getString(6));
                s.setNovaltyStatus(rs.getInt(7));
                s.setPublishDate(rs.getTimestamp(8));
                s.setCategoryId(rs.getString(9));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return s;
    }
}